package br.jonasLab.financistoweb.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import br.jonasLab.financistoweb.utils.Utils;


public class LegadoDAO extends BaseDAO{
	
	public HashMap<String, Double> legadoMes() throws SQLException{
		HashMap<String, Double> list = new HashMap<String, Double>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("	strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') AS MES_ANO, ");
		sql.append("	SUM(CASE WHEN trf.category_id =31 AND trf.from_account_id IN (1,2,12) THEN (trf.from_amount) ELSE 0 END) AS VR_LEGADO  ");
		sql.append("FROM transactions trf ");
		sql.append("WHERE trf.category_id = 31 ");
		sql.append("GROUP BY strftime('%m/%Y', trf.datetime/1000, 'unixepoch', 'localtime') ");
		sql.append("ORDER BY trf.datetime;");
		PreparedStatement ps = this.getConnection().prepareStatement(sql.toString());
		ResultSet rs = ps.executeQuery();
		while (rs != null && rs.next()) {
			list.put(rs.getString("MES_ANO"), Utils.divideValores(rs.getDouble("VR_LEGADO"),100,2));
		}
		rs.close();
        ps.close();
		return list;
	}
}
